﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Northwind.NET.Model;
using Northwind.NET.Settings;
using System.Data.Common;
using System.Data;

namespace Northwind.NET.BLL
{
    [System.ComponentModel.DataObject()]
    public class Reports
    {

        static Reports()
        {
            System.Configuration.ConnectionStringSettings connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NosDbConnection"];

            ConnectionString = connectionString.ConnectionString;
            // Loads the factory
            Factory = System.Data.Common.DbProviderFactories.GetFactory(connectionString.ProviderName);
        }

        //public static dynamic SalesAnalysisReport()
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //    var OrderSubtotals = from d in _context.Order_Details
        //                         group d by d.OrderID into g
        //                         select new
        //                         {
        //                             OrderID = g.Key,
        //                             Subtotal = g.Sum(d => (float?)d.UnitPrice * d.Quantity * (1 - d.Discount))
        //                         };
        //    var reportDataSet = from d in OrderSubtotals
        //                        join o in _context.Orders on d.OrderID equals o.OrderID
        //                        join e in _context.Employees on o.EmployeeID equals e.EmployeeID
        //                        where (o.ShippedDate != null)
        //                        orderby e.LastName ascending, e.FirstName ascending,
        //                                o.ShippedDate ascending, o.OrderID ascending
        //                        let monthNum = ((DateTime)o.ShippedDate).Month
        //                        select new
        //                        {
        //                            e.Country,
        //                            e.LastName,
        //                            e.FirstName,
        //                            ShippedYearNum = ((DateTime)o.ShippedDate).Year,
        //                            ShippedQuarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
        //                                         (monthNum >= 4 && monthNum <= 6) ? 2 :
        //                                         (monthNum >= 7 && monthNum <= 9) ? 3 :
        //                                         (monthNum >= 10 && monthNum <= 12) ? 4 : -1,
        //                            ShippedMonthNum = monthNum,
        //                            o.ShippedDate,
        //                            OrderID = o.OrderID,
        //                            SaleAmount = d.Subtotal
        //                        };


        //    return reportDataSet; 

        //} 

        //public static dynamic EmployeeSalesByCountryReport(DateTime? dateFrom, DateTime? dateTo)
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //    var orderTotals = from d in _context.Order_Details
        //                      group d by d.OrderID into g
        //                      select new
        //                      {
        //                          OrderID = g.Key,
        //                          OrderTotal = g.Sum(d => (float?)d.UnitPrice * d.Quantity * (1 - d.Discount))
        //                      };
        //    var reportDataSet = from o in _context.Orders
        //                        join d in orderTotals on o.OrderID equals d.OrderID
        //                        join e in _context.Employees on o.EmployeeID equals e.EmployeeID
        //                        where o.ShippedDate != null &&
        //                              (dateFrom == null && dateTo == null) ||
        //                              (o.ShippedDate >= dateFrom && o.ShippedDate <= dateTo)
        //                        select new
        //                        {
        //                            e.Country,
        //                            e.LastName,
        //                            e.FirstName,
        //                            o.ShippedDate,
        //                            OrderID = o.OrderID,
        //                            d.OrderTotal
        //                        };
            
        //    return reportDataSet;
        //} 


        //public static dynamic SalesByYearReport(int? yearNumArg)
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //    var udf_OrderSubtotals = from d in _context.Order_Details
        //                             group d by d.OrderID into g
        //                             select new
        //                             {
        //                                 OrderID = g.Key,
        //                                 Subtotal = g.Sum(d => (float?)d.UnitPrice * d.Quantity * (1 - d.Discount))
        //                             };

        //    var reportDataSet = from d in udf_OrderSubtotals
        //                        join o in _context.Orders on d.OrderID equals o.OrderID
        //                        let yearNum = ((DateTime)o.ShippedDate).Year
        //                        let monthNum = ((DateTime)o.ShippedDate).Month
        //                        let quarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
        //                                         (monthNum >= 4 && monthNum <= 6) ? 2 :
        //                                         (monthNum >= 7 && monthNum <= 9) ? 3 :
        //                                         (monthNum >= 10 && monthNum <= 12) ? 4 : -1
        //                        where (yearNumArg == null || yearNum == yearNumArg) &&
        //                               o.ShippedDate != null
        //                        select new
        //                        {
        //                            o.ShippedDate,
        //                            d.OrderID,
        //                            d.Subtotal,
        //                            QuarterNum = quarterNum,
        //                            Year = yearNum
        //                        };

        ////  <DataField>ShippedDate</DataField>
        ////  <rd:TypeName>System.DateTime</rd:TypeName>
        ////</Field>
        ////<Field Name="OrderID">
        ////  <DataField>OrderID</DataField>
        ////  <rd:TypeName>System.Int32</rd:TypeName>
        ////</Field>
        ////<Field Name="Subtotal">
        ////  <DataField>Subtotal</DataField>
        ////  <rd:TypeName>System.Decimal</rd:TypeName>
        ////</Field>
        ////<Field Name="Year">
        ////  <DataField>Year</DataField>
        ////  <rd:TypeName>System.Int32</rd:TypeName>
        ////</Field>
        ////<Field Name="QuarterNum">
        ////  <DataField>QuarterNum</DataField>
        ////  <rd:TypeName>System.Int32</rd:TypeName>


        //    return reportDataSet; 
        //} 


        //public static dynamic SalesTotalsByAmountReport(int? yearNumArg)
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //   var reportDataSet1 = from d in _context.Order_Details
        //                         group d by d.OrderID into g
        //                         select new
        //                         {
        //                             OrderId = g.Key,
        //                             OrderTotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
        //                         };
        //    var reportDataSet2 = from d in reportDataSet1
        //                         join o in _context.Orders on d.OrderId equals o.OrderID
        //                         join c in _context.Customers on o.CustomerID equals c.CustomerID
        //                         let yearNum = (o.OrderDate != null) ? ((DateTime)o.OrderDate).Year : -1
        //                         where yearNumArg == null || yearNum == yearNumArg
        //                         orderby d.OrderTotal descending
        //                         select new
        //                         {
        //                             SaleAmount = d.OrderTotal,
        //                             o.CustomerID,
        //                             c.ContactName,
        //                             o.ShippedDate,
        //                             GroupValue = (int)d.OrderTotal / 1000,
        //                             OrderYear = yearNum
        //                         };

        //    return reportDataSet2;



        //}        


        //public static dynamic  SalesTotalsByAmountReport1()
        //{
        //    //SimpleReportsDataSet_SalesTotalsByAmountReport

        //    //SELECT [OrderSubTotals].Subtotal AS SaleAmount, 
        //    //[Order].ID, Customer.Name, [Order].ShippedDate, 
        //    //CLNG(Fix([OrderSubTotals].Subtotal/1000)) AS GroupValue
        //    //FROM Customer, [Order], [SELECT [OrderDetail].OrderID, 
        //    //Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal 
        //    //FROM OrderDetail GROUP BY [OrderDetail].OrderID]. AS OrderSubTotals
        //    //WHERE Customer.ID=Order.CustomerID And Order.ID=OrderSubTotals.OrderId 
        //    //And (OrderSubtotals.Subtotal>2500) 
        //    //And (Order.ShippedDate Between #1/1/1997# And #12/31/1997#)
        //    //ORDER BY [OrderSubTotals].Subtotal DESC;

        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //    int? yearNumArg = null;

        //    //SELECT     [Order].ID AS OrderID, Customer.Name AS CustomerName, [Order].OrderDate, YEAR([Order].OrderDate) AS OrderYear, OrderTotals.OrderTotal
        //    //FROM         Customer INNER JOIN
        //    //                      [Order] ON Customer.ID = [Order].CustomerId INNER JOIN
        //    // (SELECT     OrderID, SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) AS OrderTotal
        //    //             FROM          OrderDetail
        //    //  GROUP BY OrderID) 
        //    // AS OrderTotals ON [Order].ID = OrderTotals.OrderID
        //    //WHERE     (YEAR([Order].OrderDate) = @OrderYear) AND (OrderTotals.OrderTotal &gt; @OrderTotal)

        //    var reportDataSet1 = from d in _context.Order_Details
        //                         group d by d.OrderID into g
        //                         select new
        //                         {
        //                             OrderId = g.Key,
        //                             OrderTotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
        //                         };
        //    var reportDataSet2 = from d in reportDataSet1
        //                         join o in _context.Orders on d.OrderId equals o.OrderID
        //                         join c in _context.Customers on o.CustomerID equals c.CustomerID
        //                         let yearNum = (o.OrderDate != null) ? ((DateTime)o.OrderDate).Year : -1
        //                         where yearNumArg == null || yearNum == yearNumArg
        //                         orderby d.OrderTotal descending
        //                         select new
        //                         {
        //                             OrderID = o.OrderID,
        //                             CustomerName = c.ContactName,
        //                             o.OrderDate,
        //                             OrderYear = yearNum,
        //                             d.OrderTotal
        //                         };


        //    return reportDataSet2; 

        //}        
 

        public class ProductCategoriesRow
        {
            public int? CategoryID { get; set; }
            public string CategoryName { get; set; } 
        }

        public static List<ProductCategoriesRow> ProductCategories()
        {
            List<string> list = new List<string>();

            //NorthwindNETEntities _context = Globals.CreateObjectContext();
            //var reportDataSet = from c in _context.Categories
            //                    select new ProductCategoriesRow 
            //                    {
            //                         CategoryID = c.CategoryID, 
            //                         CategoryName = c.CategoryName 
            //                    };

            //return reportDataSet.ToList<ProductCategoriesRow>();   
            return null;
        }

        //public static dynamic SalesByCategory(int? categoryId)
        //{
        //    //SimpleReportsDataSet_SalesByCategory

        //    //SELECT [OrderDetail].OrderID, [OrderDetail].ProductID, Product.Name AS ProductName, [OrderDetail].UnitPrice,
        //    //[OrderDetail].Quantity, [OrderDetail].Discount, CCur(([OrderDetail].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ExtendedPrice
        //    //FROM Product, OrderDetail
        //    //WHERE Product.ID=OrderDetail.ProductID;


        //    //PARAMETERS categoryId Long;
        //    //SELECT Product.CategoryID, Category.Name AS CategoryName, Product.Name AS ProductName, 
        //    //Sum([OrderDetailsExtended].ExtendedPrice) AS ProductSales
        //    //FROM Category, Product, [Order], 
        //    //[SELECT * FROM udf_OrderDetailsExtended]. AS OrderDetailsExtended
        //    //WHERE Order.ID=OrderDetailsExtended.OrderID And Product.ID=OrderDetailsExtended.ProductID And
        //    // Category.ID=Product.CategoryID And (categoryId Is Null Or Category.ID=categoryId)
        //    //GROUP BY Product.CategoryID, Category.Name, Product.Name
        //    //ORDER BY Category.Name;

        //    //TODO: optimize

        //    NorthwindNETEntities _context = Globals.CreateObjectContext();
        //    var udf_OrderDetailsExtended = from d in _context.Order_Details
        //                                   join p in _context.Products
        //                                    on d.ProductID equals p.ProductID
        //                                   select new
        //                                   {
        //                                       d.OrderID,
        //                                       d.ProductID,
        //                                       ProductName = p.ProductName,
        //                                       UnitPrice = (float?)d.UnitPrice,
        //                                       d.Quantity,
        //                                       d.Discount,
        //                                       ExtendedPrice = (float?)d.UnitPrice * d.Quantity * (1 - d.Discount)
        //                                   };

        //    const string DELIMITER = "{***}";
        //    var reportDataSet1 = from r in udf_OrderDetailsExtended
        //                         join o in _context.Orders on r.OrderID equals o.OrderID
        //                         join p in _context.Products on r.ProductID equals p.ProductID
        //                         join c in _context.Categories on p.CategoryID equals c.CategoryID
        //                         where categoryId == null || c.ID == categoryId 
        //                         select new
        //                         {
        //                             GroupKey = c.Name + DELIMITER + p.Name,
        //                             p.CategoryID,
        //                             CategoryName = c.Name,
        //                             ProductName = p.Name,
        //                             r.ExtendedPrice
        //                             //Sum([OrderDetailsExtended].ExtendedPrice) AS ProductSales
        //                         };

        //    var reportDataSet2 = from r in reportDataSet1
        //                         group r by r.GroupKey into g
        //                         select new
        //                         {
        //                             g.Key,
        //                             ProductSales = g.Sum(r => r.ExtendedPrice)
        //                         };

        //    var reportDataSet3 = from r in reportDataSet2
        //                         let pos = r.Key.IndexOf(DELIMITER)
        //                         let categoryName = r.Key.Substring(0, pos)
        //                         let productName = r.Key.Substring(pos + DELIMITER.Length)
        //                         join c in _context.Categories on categoryName equals c.Name
        //                         orderby c.Name
        //                         select new
        //                         {
        //                             GroupKey = r.Key,
        //                             CategoryId = c.ID,
        //                             CategoryName = categoryName,
        //                             ProductName = productName,
        //                             r.ProductSales
        //                         };
        //    return reportDataSet3;
        //}

        //public static dynamic SummaryOfSalesByQuarter()
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //   var reportDataSet1 = from d in _context.OrderDetail
        //                         group d by d.OrderID into g
        //                         select new
        //                         {
        //                             OrderId = g.Key,
        //                             Subtotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
        //                         };
        //    var reportDataSet2 = from o in _context.Order
        //                         join d in reportDataSet1 on o.ID equals d.OrderId
        //                         where o.ShippedDate != null
        //                         select new
        //                         {
        //                             o.ShippedDate,
        //                             OrderId = o.ID,
        //                             d.Subtotal
        //                         };

        //    var reportDataSet3 = from r in reportDataSet2
        //                         let yearNum = ((DateTime)r.ShippedDate).Year
        //                         let monthNum = ((DateTime)r.ShippedDate).Month
        //                         let quarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
        //                                          (monthNum >= 4 && monthNum <= 6) ? 2 :
        //                                          (monthNum >= 7 && monthNum <= 9) ? 3 :
        //                                          (monthNum >= 10 && monthNum <= 12) ? 4 : -1
        //                         let groupKey = quarterNum * 10000 + yearNum
        //                         select new
        //                         {
        //                             GroupByKey = groupKey,
        //                             YearNum = yearNum,
        //                             MonthNum = monthNum,
        //                             QuarterNum = quarterNum,
        //                             r.OrderId,
        //                             r.Subtotal
        //                         };

        //    var reportDataSet4 = from r in reportDataSet3
        //                         group r by r.GroupByKey into g
        //                         select new
        //                         {
        //                             GroupKey = g.Key,
        //                             OrdersCount = g.Count(r => r.OrderId != null),
        //                             SalesAmount = g.Sum(r => r.Subtotal)
        //                         };

        //    var reportDataSet5 = from r in reportDataSet4
        //                         let yearNum = r.GroupKey - ((r.GroupKey / 10000) * 10000)
        //                         let quarterNum = r.GroupKey / 10000
        //                         orderby r.GroupKey
        //                         select new
        //                         {
        //                             r.GroupKey,
        //                             YearNum = yearNum,
        //                             QuarterNum = quarterNum,
        //                             r.OrdersCount,
        //                             r.SalesAmount
        //                         };            
        //    return reportDataSet5;
        //}


        //public static dynamic SummaryOfSalesByYear()
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //    var reportDataSet1 = from d in _context.OrderDetail
        //                         group d by d.OrderID into g
        //                         select new
        //                         {
        //                             OrderId = g.Key,
        //                             Subtotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
        //                         };
        //    var reportDataSet2 = from o in _context.Order
        //                         join d in reportDataSet1 on o.ID equals d.OrderId
        //                         where o.ShippedDate != null
        //                         select new
        //                         {
        //                             o.ShippedDate,
        //                             OrderId = o.ID,
        //                             d.Subtotal
        //                         };

        //    var reportDataSet3 = from r in reportDataSet2
        //                         let yearNum = ((DateTime)r.ShippedDate).Year
        //                         let monthNum = ((DateTime)r.ShippedDate).Month
        //                         let quarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
        //                                          (monthNum >= 4 && monthNum <= 6) ? 2 :
        //                                          (monthNum >= 7 && monthNum <= 9) ? 3 :
        //                                          (monthNum >= 10 && monthNum <= 12) ? 4 : -1
        //                         let groupKey = yearNum * 10 + quarterNum
        //                         select new
        //                         {
        //                             GroupByKey = groupKey,
        //                             YearNum = yearNum,
        //                             MonthNum = monthNum,
        //                             QuarterNum = quarterNum,
        //                             r.OrderId,
        //                             r.Subtotal
        //                         };

        //    var reportDataSet4 = from r in reportDataSet3
        //                         group r by r.GroupByKey into g
        //                         select new
        //                         {
        //                             GroupKey = g.Key,
        //                             OrdersCount = g.Count(r => r.OrderId != null),
        //                             SalesAmount = g.Sum(r => r.Subtotal)
        //                         };

        //    var reportDataSet5 = from r in reportDataSet4
        //                         let yearNum = r.GroupKey / 10
        //                         let quarterNum = r.GroupKey - yearNum * 10
        //                         orderby r.GroupKey
        //                         select new
        //                         {
        //                             r.GroupKey,
        //                             YearNum = yearNum,
        //                             QuarterNum = quarterNum,
        //                             r.OrdersCount,
        //                             r.SalesAmount
        //                         };
        //    return reportDataSet5; 
        //}

        //public static dynamic Invoice(int? orderId)
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();
        //    var reportDataSet = from o in _context.Order
        //                        join d in _context.OrderDetail on o.ID equals d.OrderID
        //                        join p in _context.Product on d.ProductID equals p.ID
        //                        join c in _context.Customer on o.CustomerId equals c.ID
        //                        join e in _context.Employee on o.EmployeeID equals e.ID
        //                        join s in _context.Shipper on o.ShipperId equals s.ID
        //                        where (orderId == null || o.ID == orderId)
        //                        select new
        //                        {
        //                            o.ID,
        //                            o.CustomerId,
        //                            o.ShipName,
        //                            o.ShipAddress,
        //                            o.ShipCity,
        //                            o.ShipRegion,
        //                            o.ShipPostalCode,
        //                            o.ShipCountry,
        //                            o.OrderDate,
        //                            o.RequiredDate,
        //                            o.ShippedDate,
        //                            d.UnitPrice,
        //                            d.Quantity,
        //                            d.Discount,
        //                            ExtendedPrice = (float?)d.UnitPrice * d.Quantity * (1 - d.Discount),
        //                            o.Freight,
        //                            d.ProductID,
        //                            o.ShipperId,
        //                            o.EmployeeID,
        //                            d.OrderID,
        //                            SalesPerson = e.FirstName + " " + e.LastName,
        //                            c.ContactName,
        //                            c.Address,
        //                            c.City,
        //                            c.Region,
        //                            c.PostalCode,
        //                            c.Country,
        //                            ProductName = p.Name,
        //                            ShipVia = s.Name
        //                        };
        //    return reportDataSet;
        //}

        //public static List<string> CustomerCountries()
        //{
        //    List<string> list = new List<string>();

        //    NorthwindNETEntities _context = Globals.CreateObjectContext();
        //    var reportDataSet = from c in _context.Customer
        //                        group c by c.Country into g
        //                        select new
        //                        {
        //                            CountryName = g.Key
        //                        };

        //    foreach (var c in reportDataSet)
        //    {
        //        list.Add(c.CountryName);
        //    }

        //    return list;
        //}

        //public static dynamic CustomerLabels(string countryName)
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();
        //    var reportDataSet = from c in _context.Customer
        //                        where string.IsNullOrEmpty(countryName) || 
        //                              string.Compare(c.Country, countryName, true) == 0
        //                        select new
        //                        {
        //                            c.ID,
        //                            c.Code,
        //                            c.Name,
        //                            c.Address,
        //                            c.City,
        //                            c.Region,
        //                            c.PostalCode,
        //                            c.Country
        //                        };

        //    //List<string> list = new List<string>(); 
        //    //foreach (var r in reportDataSet)
        //    //{
        //    //    list.Add(r.Name);  
        //    //}
        //    return reportDataSet;
        //}

        public static dynamic ProductByCategory()
        {

            using (DbConnection connection = Factory.CreateConnection())
            {
                List<ProductCategory> list = new List<ProductCategory>();
                connection.ConnectionString = ConnectionString;
                connection.Open();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT Name AS ProductName, Category.Name AS CategoryName, Category.ID, QuantityPerUnit, UnitsInStock FROM Products";

                IDataReader reader = command.ExecuteReader();
                while(reader.Read())
                {
                    // read it here
                    ProductCategory p = new ProductCategory();
                    p.Name = reader.GetString(0);
                    p.CategoryName = reader.GetString(1);
                    p.CategoryID = reader.GetInt32(2);
                    p.QuantityPerUnit= reader.GetString(3);
                    p.UnitsInStock= reader.GetInt16(4);
                    list.Add(p);
                }
                return list;

             }

            //var reportDataSet = from c in _context.Category
            //                    join p in _context.Product
            //                    on c.ID equals p.CategoryID
            //                    select new
            //                    {
            //                        SortGroupLetter = p.Name.Substring(0, 1),
            //                        ProductName = p.Name,
            //                        CategoryName = c.Name,
            //                        p.QuantityPerUnit,
            //                        p.UnitsInStock
            //                    };
            //return reportDataSet; 
        }

        //public class ShippersReportRow
        //{
        //    public int ShipperID { get; internal set; }
        //    public string ShipperName { get; internal set; }
        //    public string Phone { get; internal set; }

        //}

        //public static List<ShippersReportRow> Shippers()
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();
        //    var reportDataSet = from s in _context.Shipper
        //                        select new ShippersReportRow
        //                        {
        //                            ShipperID = s.ID,
        //                            ShipperName = s.Name,
        //                            Phone = s.Phone //,
        //                            //s.RowTimeStamp
        //                        };
        //    return reportDataSet.ToList <ShippersReportRow>(); 
        //}

        //public static dynamic Catalog()
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //    //SELECT Category.ID AS CategoryId, Category.Name AS CategoryName, 
        //    // Category.Description, Category.Picture, Product.ID AS ProductId,
        //    // Product.Name AS ProductName, Product.QuantityPerUnit, Product.UnitPrice
        //    //FROM Category, Product
        //    //WHERE Category.ID = Product.CategoryId;
        //    var reportDataSet = from c in _context.Category
        //                        join p in _context.Product
        //                        on c.ID equals p.CategoryID
        //                        select new
        //                        {
        //                            CategoryId = c.ID,
        //                            CategoryName = c.Name,
        //                            c.Description,
        //                            c.Picture,
        //                            ProductId = p.ID,
        //                            ProductName = p.Name,
        //                            p.QuantityPerUnit,
        //                            p.UnitPrice
        //                        };
        //    return reportDataSet;
        //}


        //[System.ComponentModel.DataObject()]
        //public class AlphabeticalListOfProductsReportRow
        //{
        //    public string SortGroupLetter { get; internal set; }
        //    public string ProductName { get; internal set; }
        //    public string CategoryName { get; internal set; }
        //    public string QuantityPerUnit { get; internal set; }
        //    public short? UnitsInStock { get; internal set; }

        //    [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        //    public static List<AlphabeticalListOfProductsReportRow> GetReportDataSet()
        //    {
        //        // old style
        //        return Reports.AlphabeticalListOfProducts().ToList<AlphabeticalListOfProductsReportRow>();
        //    }
        //}
        
        //[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        //public static IQueryable<AlphabeticalListOfProductsReportRow> AlphabeticalListOfProducts()
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //    //vw_AlphabeticalListOfProductsReport
        //    //SELECT Mid(p.Name,1,1) AS SortGroupLetter, p.Name AS ProductName, c.Name AS CategoryName, p.QuantityPerUnit, p.UnitsInStock
        //    //FROM Category AS c, Product AS p
        //    //WHERE c.ID=p.CategoryId
        //    var vw_AlphabeticalListOfProductsReport = 
        //                  from c in _context.Category
        //                  join p in _context.Product
        //                  on c.ID equals p.CategoryID
        //                  select new AlphabeticalListOfProductsReportRow
        //                  {
        //                      SortGroupLetter = p.Name.Substring(0, 1) + "+", // "+" added to test
        //                      ProductName = p.Name,
        //                      CategoryName = c.Name,
        //                      QuantityPerUnit = p.QuantityPerUnit,
        //                      UnitsInStock = p.UnitsInStock
        //                  };
        //    return vw_AlphabeticalListOfProductsReport;
        //}

        //[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        //public static dynamic AlphabeticalListOfProducts_DynamicDataSet()
        //{
        //    NorthwindNETEntities _context = Globals.CreateObjectContext();

        //    //vw_AlphabeticalListOfProductsReport
        //    //SELECT Mid(p.Name,1,1) AS SortGroupLetter, p.Name AS ProductName, c.Name AS CategoryName, p.QuantityPerUnit, p.UnitsInStock
        //    //FROM Category AS c, Product AS p
        //    //WHERE c.ID=p.CategoryId
        //    var vw_AlphabeticalListOfProductsReport =
        //                  from c in _context.Category
        //                  join p in _context.Product
        //                  on c.ID equals p.CategoryID
        //                  select new //AlphabeticalListOfProductsReportRow
        //                  {
        //                      SortGroupLetter = p.Name.Substring(0, 1) + "++", // "++" added to test
        //                      ProductName = p.Name,
        //                      CategoryName = c.Name,
        //                      QuantityPerUnit = p.QuantityPerUnit,
        //                      UnitsInStock = p.UnitsInStock
        //                  };
        //    return vw_AlphabeticalListOfProductsReport;
        //}


        public static string ConnectionString { get; set; }

        public static DbProviderFactory Factory { get; set; }
    }
}
